package com.sample.javabean;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import com.sample.action.form.InstitutionForm;
import com.sample.jdbc.JdbcUtils;
import com.sample.util.Pager;

/**
 * 农合经办点类
 * @author 深深
 * @version 1.0
 *
 */
public class Institution {
	private String in_ins_no;//所属行政编号
    private String ins_no;//经办机构编号
    private String ins_name;//机构名称
    private int ins_level;//机构登记
    
    private JdbcUtils jdbcUtils =new JdbcUtils();
    
    /**
	 * 添加经办点
	 * @param form
	 * @throws Exception
	 */
	public void addIns(InstitutionForm form)throws Exception{
		Connection conn = null;
		Statement stmt = null;
		try {
			conn = jdbcUtils.getConn();
			stmt = conn.createStatement();
			// 3:构造插入SQL
			String sql = "insert into t_institution(in_ins_no,ins_no,ins_name,ins_level) values('"
					    + form.getIn_ins_no() + "','" +form.getIns_no()+"','"+form.getIns_name()+"','"+form.getIns_level()+"')";
			// 4:执行SQL并返回结果
			stmt.executeUpdate(sql);
			// 5:关闭资源
		} catch (SQLException e) {
			 e.printStackTrace();
			 throw new Exception("添加经办点失败，Exception_bean:<--" + e+"->");
		} finally {
			jdbcUtils.close(conn, stmt);
		}
		
	}
	
	/**
	 * 删除经办点
	 * @throws Exception
	 */
	public void delIns()throws Exception{
		Connection conn = null;
		Statement stmt = null;
		try {
			conn = jdbcUtils.getConn();
			stmt = conn.createStatement();
			// 3:构造插入SQL
			String sql = "delete from t_institution where in_ins_no='"+this.in_ins_no+"'";
			// 4:执行SQL并返回结果
			stmt.executeUpdate(sql);
			// 5:关闭资源
		} catch (SQLException e) {
			 
			 e.printStackTrace();
			 throw new Exception("删除经办点失败，Exception_bean:<--" + e+"->");
		} finally {
			jdbcUtils.close(conn, stmt);
		}
		
	}
	/**
	 * 加载经办点信息
	 * @throws Exception
	 */
	public void load()throws Exception{
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs =null; 
		try {
			conn = jdbcUtils.getConn();
			stmt = conn.createStatement();
			// 3:构造插入SQL
			String sql = "select * from  t_institution where in_ins_no='"+this.in_ins_no+"'";
			// 4:执行SQL并返回结果
			rs=stmt.executeQuery(sql);
			if(rs.next()){
				this.in_ins_no = rs.getString("in_ins_no");
				this.ins_no = rs.getString("ins_no");
				this.ins_name = rs.getString("ins_name");
				this.ins_level = rs.getInt("ins_level");
				
			}
			// 5:关闭资源
		} catch (SQLException e) {
			
			e.printStackTrace();
			throw new Exception("加载经办点信息失败！Exception_bean:<--"+e+"-->"); 
		} finally {
			jdbcUtils.close(conn, stmt);
		}	
	}
	
	/**
	 * 修改经办点
	 * @param form
	 * @throws Exception
	 */
	public void editIns(InstitutionForm form)throws Exception{
		Connection conn = null;
		Statement stmt = null;
		try {
			conn = jdbcUtils.getConn();
			stmt = conn.createStatement();
			if(form!=null){
			// 3:构造插入SQL
			String sql = "update  t_institution set ins_no='"+form.getIns_no()+"', ins_name='"+form.getIns_name()+
					"', ins_level='"+form.getIns_level()+"' where in_ins_no ='"+form.getIn_ins_no()+"'";
			// 4:执行SQL并返回结果
			stmt.executeUpdate(sql);}
			// 5:关闭资源
		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception("编辑行政区域信息失败，Exception_bean:<--"+e+"-->");
			
		} finally {
			jdbcUtils.close(conn, stmt);
		}
	}
	/**
	 * 分页
	 * @param page
	 * @param size
	 * @param form
	 * @return
	 * @throws Exception
	 */
	public Pager<Institution> list(int page,int size,InstitutionForm form)throws Exception {

		Connection conn = null;
		Statement stmt = null;
		ResultSet rs =null;
		Pager<Institution> institutions=new Pager<Institution>();
		institutions.setPage(page);
		institutions.setSize(size);
		try {
			conn = jdbcUtils.getConn();
			stmt = conn.createStatement();
			int start=(page-1)*size;
			int limit=size;
			//查询SQL
			String selectSql="select * from t_institution where 1=1";
			if(form!=null&&form.getIns_name()!=null&&!"".equals(form.getIns_name())){
				selectSql +=" and ins_name like'%"+form.getIns_name()+"%'";
			}
			//统计SQL
			String countSql ="select count(*)as c from ("+selectSql+") as t1";
			//分页SQL
			 String pageSql=selectSql+" limit "+start+","+limit;
			 rs= stmt.executeQuery(countSql);
			 int total=0;
			 while(rs.next()){
				 total=rs.getInt("c");
			 }
			 institutions.setTotal(total);
			 //查询分页信息
			 rs= stmt.executeQuery(pageSql);
			 while(rs.next()){
			
				 String in_ins_no = rs.getString("in_ins_no");
				 String ins_no = rs.getString("ins_no");
				 String ins_name = rs.getString("ins_name");
				 int ins_level = rs.getInt("ins_level");
				 Institution institution = new Institution(in_ins_no, ins_no, ins_name, ins_level);
				  institutions.getDataSets().add(institution);
			 }
			 institutions.init();
			// 5:关闭资源
		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception("Exception_bean:" +e); 
		} finally {
			jdbcUtils.close(conn, stmt);
		}
		return institutions;

	}
	
	/**
	 * 下拉列表
	 * @return
	 */
	public List<Institution> findAll()throws Exception{
		Connection conn=null;
		Statement stmt =null;
		ResultSet rs =null;
		List<Institution> insList =new ArrayList<Institution>(20);
		try{
			 conn =jdbcUtils.getConn();
			 stmt =conn.createStatement();
			//构造SQL语句
			String sql="select * from t_institution where 1=1";
			 rs =stmt.executeQuery(sql);
			while(rs.next()){
				String in_ins_no = rs.getString("in_ins_no");
				String ins_no = rs.getString("ins_no");
				String ins_name = rs.getString("ins_name");
				int ins_level = rs.getInt("ins_level");
				Institution ins = new Institution(in_ins_no, ins_no, ins_name, ins_level);
				//调用顺序列表的add方法，加入元素
				insList.add(ins);
				
			}
			
		}catch (Exception e) {
			e.printStackTrace();
			throw new Exception("获取下拉列表失败失败，Exception_bean:<--"+e+"-->");
			
		}finally{
			jdbcUtils.close(conn, stmt,rs);
		}
		return insList;
	}
//get/set方法
	public String getIn_ins_no() {
		return in_ins_no;
	}

	public void setIn_ins_no(String in_ins_no) {
		this.in_ins_no = in_ins_no;
	}

	public String getIns_no() {
		return ins_no;
	}

	public void setIns_no(String ins_no) {
		this.ins_no = ins_no;
	}

	public String getIns_name() {
		return ins_name;
	}

	public void setIns_name(String ins_name) {
		this.ins_name = ins_name;
	}

	public int getIns_level() {
		return ins_level;
	}

	public void setIns_level(int ins_level) {
		this.ins_level = ins_level;
	}


    
    //构造方法
	
	public Institution() {
		super();
	}

	public Institution(String in_ins_no, String ins_no, String ins_name, int ins_level) {
		super();
		this.in_ins_no = in_ins_no;
		this.ins_no = ins_no;
		this.ins_name = ins_name;
		this.ins_level = ins_level;
	}
	

}
